package org.xiangxji.snippets.file;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.commons.lang.StringUtils;
import org.junit.Test;
import org.xiangxji.snippets.db.DBConnectUtils;

public class CSVReader {
	
	public InputStream readCSVFile(String file) {
		return CSVReader.class.getResourceAsStream(file);
	}

	public void readLine(InputStream in) {
		BufferedReader dr=new BufferedReader(new InputStreamReader(in));
		String buf = "";
		
		try {
			while(StringUtils.isNotBlank(buf=dr.readLine())) {
				StringBuffer sql = new StringBuffer();
				String[] temps = buf.split(",");
				if(temps[0].contains("taobao.")){
					//0 1 3 4 5 13 14 15 16 17 
					sql.append("insert into apireport(NAME,ALLCOUNT,SUCCESS,SUCCESSRATE,FALUECOUNT,TOPERROR,ISPERROR,ISVERROR,INCOUNT,SYSERROR) values(");
					
					sql.append("'");sql.append(temps[0]);sql.append("'");sql.append(",");
					sql.append(temps[1]);sql.append(",");
					sql.append(temps[3]);sql.append(",");
					sql.append(temps[4]);sql.append(",");
					sql.append(temps[5]);sql.append(",");
					sql.append(temps[13]);sql.append(",");
					sql.append(temps[14]);sql.append(",");
					sql.append(temps[15]);sql.append(",");
					sql.append(temps[16]);sql.append(",");
					sql.append(temps[17]);
					sql.append(")\n");
					executeSQL(sql.toString());
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public boolean executeSQL(String sql) {
		try {
			Connection conn = DBConnectUtils.getConnection("jdbc:mysql://localhost:3306/logs", "root", "");
			if(conn!=null) {
				Statement stmt = conn.createStatement();
				boolean isSuccess = stmt.execute(sql);
				conn.close();
				return isSuccess;
			}
		} catch (IOException e) {
			e.printStackTrace();
			return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return false;
	}
	@Test
	public void exec() {
		readLine(readCSVFile("/apiReport(6).csv"));
	}
}
